﻿using System;
// Copyright (c) Kona Framework Team. All rights reserved.
// For continued development and updates, visit http://www.codeplex.com/kona
// For a copy of the license please vist: http://www.codeplex.com/kona/license
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.AnalysisServices.AdomdClient;


namespace Kona.Data.Tests.AdoMdNet
{
    /// <summary>
    /// Summary description for AdoMdClassicTestFixture
    /// </summary>
    [TestClass]
    public class AdoMdClassicTestFixture
    {

        private static readonly XNamespace XMLA_NAMESPACE = "urn:schemas-microsoft-com:xml-analysis:mddataset";

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void AdoMdConnection_Open()
        {
            using (AdomdConnection Connection = new AdomdConnection("Data Source=localhost;Catalog=Adventure Works DW"))
            {
                Connection.Open();
                Assert.AreEqual(Connection.State, System.Data.ConnectionState.Open);
                Connection.Close();
            }
        }

        [TestMethod]
        public void AdoMdCommand_Execute_SimpleQuery_ToDataset()
        {
            System.Data.DataSet Results = new DataSet();
            using (AdomdConnection Connection = new AdomdConnection("Data Source=localhost;Catalog=Adventure Works DW"))
            {
                using (AdomdCommand Command = new AdomdCommand())
                {
                    Command.Connection = Connection;
                    Command.CommandText = "select [Date].[Month of Year].Children  on 0, " +
                                          "[Customer].[Customer Geography].[Country].&[United States].Children on 1 " +
                                          "from [Adventure Works] " +
                                          "where ([Measures].[Customer Count],[Date].[Fiscal Year].&[2004])";
                    AdomdDataAdapter Adapter = new AdomdDataAdapter(Command);
                    Adapter.Fill(Results);
                }
            }
            Assert.AreEqual(Results.Tables.Count, 1);
            Assert.AreEqual(Results.Tables[0].Rows.Count, 36);
        }

        [TestMethod]
        public void AdoMdCommand_Execute_SimpleQuery_XmlReader()
        {
            using (AdomdConnection Connection = new AdomdConnection("Data Source=localhost;Catalog=Adventure Works DW"))
            {
                Connection.Open();
                using (AdomdCommand Command = new AdomdCommand())
                {
                    Command.Connection = Connection;
                    Command.CommandText = "select [Date].[Month of Year].Children  on 0, " +
                                          "[Customer].[Customer Geography].[Country].&[United States].Children on 1 " +
                                          "from [Adventure Works] " +
                                          "where ([Measures].[Customer Count],[Date].[Fiscal Year].&[2004])";
                    XmlReader Reader = Command.ExecuteXmlReader();
                    XDocument Doc = XDocument.Load(Reader);
                    Doc.Save("SimpleQueryResults.xml");
                }
                Connection.Close();
            }
        }

        [TestMethod]
        public void AdoMdCommand_Execute_SimpleQuery_XmlReader_AndTransform()
        {
            XElement ResultsElement;

            using (AdomdConnection Connection = new AdomdConnection("Data Source=localhost;Catalog=Adventure Works DW"))
            {
                Connection.Open();
                using (AdomdCommand Command = new AdomdCommand())
                {
                    Command.Connection = Connection;
                    Command.CommandText = "select [Date].[Month of Year].Children  on 0, " +
                                          "[Customer].[Customer Geography].[Country].&[United States].Children on 1 " +
                                          "from [Adventure Works] " +
                                          "where ([Measures].[Customer Count],[Date].[Fiscal Year].&[2004])";
                    XmlReader Reader = Command.ExecuteXmlReader();
                    Reader.ReadToDescendant("Axes", XMLA_NAMESPACE.NamespaceName);
                    System.Xml.XmlReader SubTreeReader = Reader.ReadSubtree();
                    XElement AxesElement = XElement.Load(SubTreeReader);
                    Reader.ReadToFollowing("CellData", XMLA_NAMESPACE.NamespaceName);
                    SubTreeReader = Reader.ReadSubtree();
                    XElement CellDataElement = XElement.Load(SubTreeReader);

                    Assert.IsNotNull(CellDataElement);
                    Assert.IsNotNull(AxesElement);

                    ResultsElement = new XElement(XMLA_NAMESPACE + "root", AxesElement, CellDataElement);
                }
                Connection.Close();
            }

            var StateProvinceMonthlyCountsList = Transform(ResultsElement);

            Assert.IsNotNull(StateProvinceMonthlyCountsList);
            Assert.AreEqual(StateProvinceMonthlyCountsList.Count, 36);
        }

        private static List<StateProvinceMonthlyCounts> Transform(XElement rootElement)
        {
            #region Parameter Validation
            if (rootElement == null)
            { return null; }

            if (rootElement.Element(XMLA_NAMESPACE + "Axes") == null)
            { return null; }

            //XElement Axis0Element = (from e in rootElement.Descendants(XMLA_NAMESPACE + "Axis")
            //                         where e.Attribute("name").Value == "Axis0"
            //                         select e).SingleOrDefault<XElement>();

            XElement Axis0Element = (from e in rootElement.Descendants(XMLA_NAMESPACE + "Axis")
                                     where e.Attribute("name").Value == "Axis0"
                                     select e).Single();
            if (Axis0Element == null)
            { return null; }

            XElement Axis1Element = (from e in rootElement.Descendants(XMLA_NAMESPACE + "Axis")
                                     where e.Attribute("name").Value == "Axis1"
                                     select e).Single();
            if (Axis1Element == null)
            { return null; }

            XElement CellDataElement = rootElement.Element(XMLA_NAMESPACE + "CellData");
            if (CellDataElement == null)
            { return null; }
            #endregion

            var Axis1MemberElements = Axis1Element.Descendants(XMLA_NAMESPACE + "Member");
            var Axis0MemberElements = Axis0Element.Descendants(XMLA_NAMESPACE + "Member");
            var AllMonthlyCustomerCounts = new List<MonthlyCustomerCount>();
            var StateProvinceMonthlyCountsList = new List<StateProvinceMonthlyCounts>();

            foreach (var RowPrototype in Axis1MemberElements)
            {
                var RowMemberUNameElement = RowPrototype.Element(XMLA_NAMESPACE + "UName");
                var RowMemberCaptionElement = RowPrototype.Element(XMLA_NAMESPACE + "Caption");
                var NewStateProvinceMonthlyCounts = new StateProvinceMonthlyCounts
                {
                    StateProvince = new StateProvince
                    {
                        Caption = RowMemberCaptionElement.Value,
                        UName = RowMemberUNameElement.Value
                    },
                    MonthlyCustomerCounts = new List<MonthlyCustomerCount>()
                };


                foreach (var ColumnPrototype in Axis0MemberElements)
                {
                    var ColumnUNameElement = ColumnPrototype.Element(XMLA_NAMESPACE + "UName");
                    var ColumnCaptionElement = ColumnPrototype.Element(XMLA_NAMESPACE + "Caption");
                    var NewMonthlyCustomerCount = new MonthlyCustomerCount
                    {
                        MonthOfYear = new MonthofYear
                        {
                            Caption = ColumnCaptionElement.Value,
                            UName = ColumnUNameElement.Value
                        }
                    };
                    AllMonthlyCustomerCounts.Add(NewMonthlyCustomerCount);
                    NewStateProvinceMonthlyCounts.MonthlyCustomerCounts.Add(NewMonthlyCustomerCount);
                }
                StateProvinceMonthlyCountsList.Add(NewStateProvinceMonthlyCounts);
            }

            foreach (var CellElement in CellDataElement.Elements(XMLA_NAMESPACE + "Cell"))
            {
                int CellOrdinalValue = Convert.ToInt32(CellElement.Attribute("CellOrdinal").Value);
                var CellValueElement = CellElement.Element(XMLA_NAMESPACE + "Value");
                int CellValue = Convert.ToInt32(CellValueElement.Value);
                var SelectedMonthlyCustomerCount = AllMonthlyCustomerCounts[CellOrdinalValue];
                SelectedMonthlyCustomerCount.Count = CellValue;
            }
            return StateProvinceMonthlyCountsList;
        }


        [TestMethod]
        public void AdoMdCommand_Execute_SimpleQuery_WithParameter()
        {
            XElement ResultsElement;

            using (AdomdConnection Connection = new AdomdConnection("Data Source=localhost;Catalog=Adventure Works DW"))
            {
                Connection.Open();
                using (AdomdCommand Command = new AdomdCommand())
                {
                    Command.Connection = Connection;
                    Command.CommandText = "select [Date].[Month of Year].Children DIMENSION PROPERTIES MEMBER_KEY  on 0, " +
                                          "[Customer].[Customer Geography].[Country].&[United States].Children DIMENSION PROPERTIES MEMBER_KEY on 1 " +
                                          "from [Adventure Works] " +
                                          "where ([Measures].[Customer Count],strtomember(@Year))";
                    AdomdParameter YearParameter = new AdomdParameter("Year", "[Date].[Fiscal Year].&[2004]");
                    Command.Parameters.Add(YearParameter);

                    XmlReader Reader = Command.ExecuteXmlReader();
                    Reader.ReadToDescendant("Axes", XMLA_NAMESPACE.NamespaceName);
                    System.Xml.XmlReader SubTreeReader = Reader.ReadSubtree();
                    XElement AxesElement = XElement.Load(SubTreeReader);
                    Reader.ReadToFollowing("CellData", XMLA_NAMESPACE.NamespaceName);
                    SubTreeReader = Reader.ReadSubtree();
                    XElement CellDataElement = XElement.Load(SubTreeReader);

                    Assert.IsNotNull(CellDataElement);
                    Assert.IsNotNull(AxesElement);

                    ResultsElement = new XElement(XMLA_NAMESPACE + "root", AxesElement, CellDataElement);
                }
                Connection.Close();
            }

            var StateProvinceMonthlyCountsList = Transform(ResultsElement);

            Assert.IsNotNull(StateProvinceMonthlyCountsList);
            Assert.AreEqual(StateProvinceMonthlyCountsList.Count, 36);
        }

    }



    public struct MonthofYear
    {
        public string UName
        {
            get;
            internal set;
        }

        public string Caption
        {
            get;
            internal set;
        }
    }

    public class MonthlyCustomerCount
    {
        public MonthofYear MonthOfYear
        {
            get;
            protected internal set;
        }

        public Int64 Count
        {
            get;
            protected internal set;
        }
    }

    public struct StateProvince
    {
        public string UName
        {
            get;
            internal set;
        }

        public string Caption
        {
            get;
            internal set;
        }
    }

    public class StateProvinceMonthlyCounts
    {
        public StateProvince StateProvince
        {
            get;
            protected internal set;
        }

        public List<MonthlyCustomerCount> MonthlyCustomerCounts
        {
            get;
            protected internal set;
        }
    }
}
